Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OLAP_EXPRESSION Examples

The following script was used to create the view `unit_cost_price_view`, which is used in Example 30-1 and Example 30-2 to illustrate the use of `OLAP_EXPRESSION`. For information about creating views of analytic workspaces, see "OLAP_TABLE Overview".

Sample View: GLOBAL.UNIT_COST_PRICE_VIEW

    -- Create the logical row
    CREATE TYPE unit_cost_price_row AS OBJECT (
                aw_unit_cost          NUMBER,
                aw_unit_price         NUMBER,
                aw_product            VARCHAR2(50),
                aw_time               VARCHAR2(20),
                r2c                   RAW(32));
    /
    -- Create the logical table
    CREATE TYPE unit_cost_price_table AS TABLE OF unit_cost_price_row;
    /
    -- Create the view
    CREATE OR REPLACE VIEW unit_cost_price_view AS
        SELECT aw_unit_cost, aw_unit_price, aw_product, aw_time, r2c
          FROM TABLE(OLAP_TABLE(
             'global DURATION SESSION',
             'unit_cost_price_table',
             '',
             'MEASURE aw_unit_cost  FROM price_cube_unit_cost
              MEASURE aw_unit_price FROM price_cube_unit_price
              DIMENSION product WITH
                 HIERARCHY product_parentrel
                    INHIERARCHY product_inhier
                 ATTRIBUTE aw_product FROM product_short_description
              DIMENSION time WITH
                 HIERARCHY time_parentrel
                    INHIERARCHY time_inhier
                 ATTRIBUTE aw_time FROM time_short_description
              ROW2CELL r2c'));
    /

The following query shows some of the aggregate data in the view.

    SQL>SELECT * FROM unit_cost_price_view
                 WHERE aw_product = 'Hardware'
                 AND aw_time in ('2000', '2001', '2002', '2003')
                 ORDER BY aw_time;

    AW_UNIT_COST    AW_UNIT_PRICE    AW_PRODUCT    AW_TIME    R2C
    ------------    -------------    ----------    -------    -----
       211680.12        224713.71      Hardware       2000    00...
       195591.60        207513.16      Hardware       2001    00...
       184413.05        194773.78      Hardware       2002    00...
        73457.31         77275.06      Hardware       2003    00...

Example 30-1 OLAP_EXPRESSION: Time Series Function in a WHERE Clause

This example uses the view described in "Sample View: GLOBAL.UNIT_COST_PRICE_VIEW".

The following `SELECT` statement calculates an expression with an alias of `PERIODAGO`, and limits the result set to calculated values greater than 50,000. The calculation uses the `LAG` function to return the value of the previous time period.

    SQL>SELECT aw_time time, aw_unit_cost unit_cost,
                OLAP_EXPRESSION(r2c,
                               'LAG(price_cube_unit_cost, 1, time,
                                    LEVELREL time_levelrel)') periodago
            FROM unit_cost_price_view
            WHERE aw_product = 'Hardware'
            AND OLAP_EXPRESSION(r2c,
                               'LAG(price_cube_unit_cost, 1, time,
                                    LEVELREL time_levelrel)') > 50000;

This `SELECT` statement produces these results.

    TIME                  UNIT_COST  PERIODAGO
    -------------------- ---------- ----------
    2003                   73457.31  184413.05
    2004                              73457.31
    1999                   231095.4  162526.92
    2000                  211680.12   231095.4
    2001                   195591.6  211680.12
    2002                  184413.05   195591.6
    Q2-99                  57587.34   57856.76
    Q3-99                  59464.25   57587.34
    Q4-99                  56187.05   59464.25
    Q1-00                  53982.32   56187.05
    Q2-00                  53629.74   53982.32
    Q3-00                  53010.65   53629.74
    Q4-00                  51057.41   53010.65
    Q1-01                  49691.22   51057.41

Example 30-2 OLAP_EXPRESSION: Numeric Calculation in an ORDER BY CLause

This example uses the view described in "Sample View: GLOBAL.UNIT_COST_PRICE_VIEW".

This example subtracts costs from price, and gives this expression an alias of `MARKUP`. The rows are ordered by markup from highest to lowest.

    SQL>SELECT aw_time time, aw_unit_cost unit_cost, aw_unit_price unit_price,
                OLAP_EXPRESSION(r2c,
                          'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') markup
         FROM unit_cost_price_view
         WHERE aw_product = 'Hardware'
         AND aw_time in ('1998', '1999', '2000', '2001')
         ORDER BY OLAP_EXPRESSION(r2c,
                          'PRICE_CUBE_UNIT_PRICE - PRICE_CUBE_UNIT_COST') DESC;

This `SELECT` statement produces these results.

    TIME                  UNIT_COST   UNIT_PRICE  MARKUP
    -------------------- ----------  ----------  ---------
    1999                  231095.40   245412.91   14317.51
    2000                  211680.12   224713.71   13033.59
    2001                  195591.60   207513.16   11921.56
    1998                  162526.92   173094.41   10567.49

# OLAP_EXPRESSION Syntax

The `OLAP_EXPRESSION` function dynamically executes an OLAP DML numeric expression within the context of an `OLAP_TABLE` function. In addition to returning a custom measure, `OLAP_EXPRESSION` can be used in the `WHERE` and `ORDER BY` clauses to modify the result set of the query of the analytic workspace.

Syntax

    OLAP_EXPRESSION(
              r2c                  IN   RAW(32),
              numeric_expression   IN   VARCHAR2)
          RETURN NUMBER;

Parameters

Table 30-1 OLAP_EXPRESSION Function Parameters

Parameter | Description
--- | ---
`r2c` | The name of a column specified by a `ROW2CELL` clause in the limit map. `OLAP_TABLE` populates this column with information used by the OLAP single-row functions, including `OLAP_EXPRESSION`. See "Limit Map Parameter".
`numeric_expression` | An OLAP DML expression that returns a numeric result. Search for "expressions" in the Oracle OLAP DML Reference. See also "Guidelines for Using Quotation Marks in OLAP DML Commands".

Returns

An evaluation of numeric_expression for each row of the table object returned by the `OLAP_TABLE` function.

`OLAP_EXPRESSION` returns numeric data. To return text, boolean, or date data, use the `OLAP_EXPRESSION_TEXT`, `OLAP_EXPRESSION_BOOL`, or `OLAP_EXPRESSION_DATE` functions.

Example

See "OLAP_EXPRESSION Examples".